﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ExcelFileQueryTool
{
    public sealed class CrmPeopleService
    {
        public string ConnectionString { get; private set; }
        public CrmPeopleService(string connStr)
        {
            if (string.IsNullOrEmpty(connStr))
                throw new ArgumentNullException("connStr");

            this.ConnectionString = connStr;
        }

        public int Add(CRMPeople data)
        {
            if (data == null)
                return 0;

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format(@"INSERT INTO [dbo].[CRM_People]
           ([Name]
           ,[IndexCode]
           ,[TypeCode]
           ,[Gender]
           ,[CreatedTime]
           ,[LastUpdatedTime]
           ,[Status]
           ,[NickName]
           ,[Telephone]
           ,[Address]
           ,[Note])
     VALUES
           (@Name
           ,@IndexCode
           ,@TypeCode
           ,@Gender
           ,@CreatedTime
           ,@LastUpdatedTime
           ,@Status
           ,@NickName
           ,@Telephone
           ,@Address
           ,@Note);
SELECT SCOPE_IDENTITY();");

                data.Id = SqlMapper.ExecuteScalar<int>(conn, sql, data);
                return data.Id.Value;
            }
        }

        public int Update(CRMPeople data)
        {
            if (data == null)
                return 0;

            if (data.Id.HasValue == false)
                throw new ArgumentNullException("data.Id", "No value");

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = @"UPDATE [dbo].[CRM_People]
   SET [Name] = @Name
      ,[IndexCode] = @IndexCode
      ,[TypeCode] = @TypeCode
      ,[Gender] = @Gender
      ,[LastUpdatedTime] = @LastUpdatedTime
      ,[NickName] = @NickName
      ,[Telephone] = @Telephone
      ,[Address] = @Address
      ,[Note] = @Note
 WHERE [Id]=@Id";

                return SqlMapper.Execute(conn, sql, data);
            }
        }

        public CRMPeople GetById(int id)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                string sql = string.Format("select * from crm_people where [Id]='{0}' and [Status]={1}", id, (short)DBRecordStatus.Normal);
                return SqlMapper.Query<CRMPeople>(conn, sql).FirstOrDefault();
            }
        }

        public CRMPeople GetByName(string name)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                string sql = string.Format("select * from crm_people where [Name]='{0}' and  [Status]={1}", name, (short)DBRecordStatus.Normal);
                return SqlMapper.Query<CRMPeople>(conn, sql).FirstOrDefault();
            }
        }

        public IEnumerable<CRMPeople> QueryList(string name_indexcode, string type, string phone)
        {
            var sql = "select * from Crm_People where [Status]=" + (short)DBRecordStatus.Normal;

            if (string.IsNullOrEmpty(name_indexcode) == false)
                sql += string.Format(" and ([Name] like '%{0}%' or [IndexCode] like '%{0}%')", name_indexcode);

            if (string.IsNullOrEmpty(type) == false)
                sql += (" and [TypeCode]='" + type + "'");

            if (string.IsNullOrEmpty(phone) == false)
                sql += " and [Telephone] like '%" + phone + "%'";

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                return SqlMapper.Query<CRMPeople>(conn, sql);
            }
        }

        public int Delete(int id)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = "update Crm_People set [Status]=" + (short)DBRecordStatus.Deleted + " where Id=" + id.ToString();
                return SqlMapper.Execute(conn, sql);
            }
        }

        public IEnumerable<string> GetAllTypeCodes()
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = "select distinct TypeCode from Crm_People where [Status]<>" + (short)DBRecordStatus.Deleted;
                return SqlMapper.Query<string>(conn, sql);
            }
        }
    }
}
